import datetime

import pymysql as sql

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllEmergency():
    print('return all cope_record')

    sql = "select risk_cope.risk_cope_id, risk_cope.time, risk_cope.risk_id, riskpoint.name, risk_cope.status " \
          "from risk_cope, riskpoint " \
          "where risk_cope.risk_id = riskpoint.risk_id"

    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_id', 'time', 'risk_id', 'name', 'status']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['time'] = dic['time'].strftime("%Y-%m-%d")
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def getEmergencyWithCopeIDFromDB(risk_cope_id):
    sql = "select emergency_id, risk_cope_id, content " \
          "from emergency " \
          "where risk_cope_id = '%s'" % risk_cope_id
    print(sql)
    cursor.execute(sql)

    attribute = ['emergency_id', 'risk_cope_id', 'content']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    return l


def editEmergencyFromDB(risk_cope_id):
    sql = "update emergency " \
          "set content = '建议送到月球' " \
          "where risk_cope_id = '%s'" % risk_cope_id
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def addEmergencyToDB(risk_cope_id, content):
    print('insert a new emergency')

    emergency_id = newEmergencyID()

    sql = "insert into emergency values('%s', '%s', '%s')" % (str(emergency_id), str(risk_cope_id), str(content))
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def newEmergencyID():
    sql = "select max(emergency_id) from emergency"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str
